ICAEW.com works better with JavaScript enabled.
Exclusive

Tip of the Week

Excel Tip of the Week #403 - Power Query - merge kinds

Author: David Lyford-Smith

Published: 20 Jul 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we are looking at the various options with Power Query’s Merge option.

If you want a refresher on Power Query, check out our recent webinar.

Merge in turn

Power Query’s Merge operation allows you to combine data from two or more data sources by reference to a key column. It’s similar to a lookup in regular Excel, only much more powerful. Here’s a basic use case:

screenshot from Excel spreadsheet

The two simple tables on the left tell us customer values and the responsible salespersons; we can merge these using the Customer ID column to create the combined table on the right. This is automated so adding to either table later on will pull through to the combined table.

When creating this merge, we chose a join kind:

screenshot from Excel spreadsheet

The kind used here was “Left Outer” – which we are told is all the rows in the first table, and those with a matching Customer ID from the second table. In previous posts in this series we’ve looked at the “Full Outer” kind – which takes all the rows from both tables. Let’s look at all the options.

Purchase orders and invoices

For this we’ll use these two small datasets:

screenshot from Excel spreadsheet

We’re going to explore what we get out of merging these two tables (the PO one first), through each of these options:

screenshot from Excel spreadsheet
Left Outer
screenshot from Excel spreadsheet

This adds the invoice received data to our PO table where it is available.

Right Outer

screenshot from Excel spreadsheet

As you would expect, this allows us to make the opposite to the Left Outer – a duplicate of the Invoices Received table, showing the PO data where we have it.

Full Outer

screenshot from Excel spreadsheet

A Full Outer brings the full detail from both tables into the merge, allowing us to create a complete invoice table.  This requires a bit more manipulation than the other kinds, as we have to merge the two incomplete Invoice number and Amount columns with custom columns.

Inner

screenshot from Excel spreadsheet

This returns only the three invoices which have been both PO’d and marked as received.

Left Anti

screenshot from Excel spreadsheet

This is a report of all the purchase orders for which we have not received an invoice.

Right Anti

screenshot from Excel spreadsheet

Finally, we have the list of invoices received that we don’t have POs for.

It’s important to think carefully about what join kind is right for your use case – not only to get the right data, but because the ordering of tables can make a difference. While in theory a Right Outer is equivalent to doing a Left Outer with the tables in the opposite order, after any join the left table is always the one that’s fully loaded and the right one is always in a collapsed Table that needs expanding. So it’s much easier to have your primary table as the first (left) table.

You can see all these examples and work through the steps in the Power Query Editor in the attached file .

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250